﻿Imports VN.INS.DL.DH
Imports VN.INS.BL.BE

Public Class Mantenimiento_MenuDA
    Public Function ListaMenu() As DataTable

        Dim Objdata As AdministradorOracle = New AdministradorOracle
        Dim Objdt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT M.ID_MENU,M.DESCRIPCION,M.PADRE_ID,M.POSICION,M.HABILITADO ,M.URL,G.CODIGO as ESTADO,PF.PERFIL,PF.DESCRIPCION AS PFDES " & _
                      "FROM MENU M " & _
                      "left join  (select CODIGO,DESCRIPCION AS ESTADO from general WHERE ID='ESTADO_PERIODO') G ON G.CODIGO=M.HABILITADO " & _
                      "left join menu_perfil MP ON MP.ID_MENU=M.ID_MENU " & _
                      "left join perfil PF ON PF.PERFIL=MP.PERFIL "

            Objdt = Objdata.ExecuteSQL(lxc_SQL, "Conexion.Oracle")

            Return Objdt

        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Public Function ListaMenuBusca(ByVal stridmenu As String, ByVal strDescripcion As String) As DataTable

        Dim Objdata As AdministradorOracle = New AdministradorOracle
        Dim Objdt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT M.ID_MENU,M.DESCRIPCION,M.PADRE_ID,M.POSICION,M.HABILITADO ,M.URL,G.CODIGO as ESTADO,PF.PERFIL,PF.DESCRIPCION AS PFDES " & _
                      "FROM MENU M " & _
                      "left join  (select CODIGO,DESCRIPCION AS ESTADO from general WHERE ID='ESTADO_PERIODO') G ON G.CODIGO=M.HABILITADO " & _
                      "left join menu_perfil MP ON MP.ID_MENU=M.ID_MENU " & _
                      "left join perfil PF ON PF.PERFIL=MP.PERFIL "
            If stridmenu <> "" Then
                lxc_SQL = lxc_SQL & " where M.ID_MENU ='" & stridmenu & "' "
            End If

            If strDescripcion <> "" Then
                If stridmenu <> "" Then
                    lxc_SQL = lxc_SQL & "AND  M.DESCRIPCION like '" & strDescripcion & "%' "
                End If
                If stridmenu = "" Then
                    lxc_SQL = lxc_SQL & "WHERE  M.DESCRIPCION like '" & strDescripcion & "%' "
                End If
            End If
            Objdt = Objdata.ExecuteSQL(lxc_SQL, "Conexion.Oracle")

            Return Objdt

        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Public Function ListaMenuPerfil() As DataTable
        Dim Objdata As AdministradorOracle = New AdministradorOracle
        Dim Objdt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT MP.ID_MENU,MP.MENU_PERFIL,MP.PERFIL,MP.ESTADO,G.CODIGO AS ESTADO  " & _
                    "FROM MENU_PERFIL MP " & _
                    "left join  (select CODIGO,DESCRIPCION AS ESTADO from general WHERE ID='ESTADO_USUARIO') G ON G.CODIGO=MP.ESTADO "

            Objdt = Objdata.ExecuteSQL(lxc_SQL, "Conexion.Oracle")

            Return Objdt

        Catch ex As Exception
            Throw ex
        End Try


    End Function
    Public Function ListaMenuPerfilBuscar(ByVal id_menu As String, ByVal menu_perfil As String, ByVal perfil As String) As DataTable
        Dim Objdata As AdministradorOracle = New AdministradorOracle
        Dim Objdt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT MP.ID_MENU,MP.MENU_PERFIL,MP.PERFIL,MP.ESTADO,G.CODIGO AS ESTADO  " & _
                    "FROM MENU_PERFIL MP " & _
                    "left join  (select CODIGO,DESCRIPCION AS ESTADO from general WHERE ID='ESTADO_USUARIO') G ON G.CODIGO=MP.ESTADO "

            If id_menu <> "" Then
                lxc_SQL = lxc_SQL & "where MP.ID_MENU='" & id_menu & "' "
            End If
            If menu_perfil <> "" Then
                If id_menu <> "" Then
                    lxc_SQL = lxc_SQL & "and MP.MENU_PERFIL='" & menu_perfil & "' "
                End If
                If id_menu = "" Or perfil = "all" Then
                    lxc_SQL = lxc_SQL & "WHERE MP.MENU_PERFIL='" & menu_perfil & "' "
                End If
            End If


            If perfil <> "all" Then
                If id_menu <> "" And menu_perfil <> "" Then
                    lxc_SQL = lxc_SQL & " AND MP.PERFIL='" & perfil & "' "
                End If
                If id_menu <> "" And menu_perfil = "" Then
                    lxc_SQL = lxc_SQL & " AND MP.PERFIL='" & perfil & "' "
                End If
                If id_menu = "" And menu_perfil <> "" Then
                    lxc_SQL = lxc_SQL & " WHERE MP.PERFIL='" & perfil & "' "
                End If
                If id_menu = "" And menu_perfil = "" Then
                    lxc_SQL = lxc_SQL & " WHERE MP.PERFIL='" & perfil & "' "
                End If
            End If
            Objdt = Objdata.ExecuteSQL(lxc_SQL, "Conexion.Oracle")

            Return Objdt

        Catch ex As Exception
            Throw ex
        End Try


    End Function

    Public Sub insertarmenu(ByVal IDMENU As String, ByVal DSC As String, ByVal PADRE As String, ByVal POSC As String, ByVal HAB As String, ByVal UR As String)
        Dim objData As AdministradorOracle = New AdministradorOracle

        Try
            objData.CreateInputOracle("IDMENU", IDMENU, 20)
            objData.CreateInputOracle("DSC", DSC, 50)
            objData.CreateInputOracle("PADRE", PADRE, 20)
            objData.CreateInputOracle("POSC", POSC, 20)
            objData.CreateInputOracle("HAB", HAB, 1)
            objData.CreateInputOracle("UR", UR, 50)

            objData.ExecuteOracle_NonQuery("insertarmenu", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Sub editarmenu(ByVal IDMENU As String, ByVal DSC As String, ByVal PADRE As String, ByVal POSC As String, ByVal HAB As String, ByVal UR As String)
        Dim objData As AdministradorOracle = New AdministradorOracle

        Try
            objData.CreateInputOracle("IDMENU", IDMENU, 20)
            objData.CreateInputOracle("DSC", DSC, 50)
            objData.CreateInputOracle("PADRE", PADRE, 20)
            objData.CreateInputOracle("POSC", POSC, 20)
            objData.CreateInputOracle("HAB", HAB, 1)
            objData.CreateInputOracle("UR", UR, 50)

            objData.ExecuteOracle_NonQuery("editamenu", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Public Sub eliminarmenu(ByVal IDMENU As String)
        Dim ObjData As AdministradorOracle = New AdministradorOracle
        Try
            ObjData.CreateInputOracle("IDMENU", IDMENU, 20)

            ObjData.ExecuteOracle_NonQuery("eliminarmenu", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Public Function ListaPerfil() As DataTable
        Dim ObjData As AdministradorOracle = New AdministradorOracle
        Dim Objdt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "select PF.PERFIL,PF.DESCRIPCION " & _
                      "from perfil pf order by perfil "

            Objdt = ObjData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return Objdt
        Catch ex As Exception
            Throw ex
        End Try


    End Function
    Public Sub insertarmenuperfil(ByVal IDMN As String, ByVal MP As String, ByVal PF As String, ByVal EST As String)
        Dim Objdata As AdministradorOracle = New AdministradorOracle

        Try
            Objdata.CreateInputOracle("IDMN", IDMN, 20)
            Objdata.CreateInputOracle("MP", MP, 50)
            Objdata.CreateInputOracle("PF", PF, 2)
            Objdata.CreateInputOracle("EST", EST, 1)

            Objdata.ExecuteOracle_NonQuery("insertarmenuperfil", "Conexion.Oracle")

        Catch ex As Exception
            Throw ex
        End Try

    End Sub

    Public Sub editarmenuperfil(ByVal IDMN As String, ByVal MP As String, ByVal PF As String, ByVal EST As String)
        Dim Objdata As AdministradorOracle = New AdministradorOracle
        Try
            Objdata.CreateInputOracle("IDMN", IDMN, 20)
            Objdata.CreateInputOracle("MP", MP, 50)
            Objdata.CreateInputOracle("PF", PF, 2)
            Objdata.CreateInputOracle("EST", EST, 1)

            Objdata.ExecuteOracle_NonQuery("EDITAMENUPERFIL", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Public Sub eliminarmenuperfil(ByVal IDMP As String)
        Dim ObjData As AdministradorOracle = New AdministradorOracle
        Try
            ObjData.CreateInputOracle("IDMP", IDMP, 20)

            ObjData.ExecuteOracle_NonQuery("eliminarmenuperfil", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try

    End Sub

    'Select para ver si es padre y tiene hijos en MENU_PERFIL
    Public Function HMP(ByVal PADRE_ID As String, ByVal PERFIL As String) As DataTable
        Dim ObjData As AdministradorOracle = New AdministradorOracle
        Dim Objdt As DataTable
        Dim lxc_SQL As String

        Try
            lxc_SQL = "SELECT MP.MENU_PERFIL,MP.ID_MENU FROM MENU M " & _
                      "INNER JOIN MENU_PERFIL MP " & _
                      "ON M.ID_MENU = MP.ID_MENU " & _
                      "WHERE M.ID_MENU IN (SELECT ID_MENU FROM MENU WHERE PADRE_ID = '" & PADRE_ID & "' AND POSICION != '0' ) AND MP.PERFIL = '" & PERFIL & "' "


            Objdt = ObjData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return Objdt
        Catch ex As Exception
            Throw ex
        End Try

    End Function

    'Select para ver si es padre y tiene hijos en MENU
    Public Function PHM(ByVal PADRE_ID As String) As DataTable
        Dim ObjData As AdministradorOracle = New AdministradorOracle
        Dim Objdt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT ID_MENU FROM MENU " & _
                      "WHERE ID_MENU !=PADRE_ID " & _
                      "AND ID_MENU IN (SELECT ID_MENU FROM MENU WHERE PADRE_ID = '" & PADRE_ID & "' AND POSICION !='0') "
            Objdt = ObjData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return Objdt
        Catch ex As Exception
            Throw ex
        End Try

    End Function

    'SELECT PARA VER SI REGISTRO MENU existe en MENU_PERFIL
    Public Function RMEMP(ByVal ID_MENU As String) As DataTable
        Dim ObjData As AdministradorOracle = New AdministradorOracle
        Dim Objdt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT MP.MENU_PERFIL,MP.ID_MENU FROM MENU_PERFIL MP " & _
                      "WHERE MP.ID_MENU = '" & ID_MENU & "' "
            Objdt = ObjData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return Objdt
        Catch ex As Exception
            Throw ex
        End Try

    End Function
End Class
